﻿using QuanLyCongDan.DTO;
using System.Data.Common;
using System.Data;
using System;
using System.Collections.Generic;
using System.Data.OleDb;

namespace DAL
{
    public class DAL_CongDan
    {
        /// <summary>
        /// Insert this object CongDan into database.
        /// </summary>
        /// <param name="congDanInput">The inserting CongDan object</param>
        /// <returns>True if insert successfully, false if not</returns>
        public static bool InsertCongDan(DTO_CongDan congDanInput)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText =
                "INSERT INTO CongDan " +
                "([TenCD], [NamSinh], [HocVan], [DanToc], [TonGiao], [ThuongTru], [NgheNghiep], [HoTenChaMe], [DonVi], [GhiChu]) " +
                "VALUES " +
                "(@TenCD, @NamSinh, @HocVan, @DanToc, @TonGiao, @ThuongTru, @NgheNghiep, @HoTenChaMe, @DonVi, @GhiChu)";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@TenCD";
            param.Value = string.IsNullOrEmpty(congDanInput.Ten) ? string.Empty : congDanInput.Ten;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@NamSinh";
            param.Value = congDanInput.NamSinh;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@HocVan";
            param.Value = string.IsNullOrEmpty(congDanInput.HocVan) ? string.Empty : congDanInput.HocVan;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@DanToc";
            param.Value = string.IsNullOrEmpty(congDanInput.DanToc) ? string.Empty : congDanInput.DanToc;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@TonGiao";
            param.Value = string.IsNullOrEmpty(congDanInput.TonGiao) ? string.Empty : congDanInput.TonGiao;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ThuongTru";
            param.Value = string.IsNullOrEmpty(congDanInput.ThuongTru) ? string.Empty : congDanInput.ThuongTru;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@NgheNghiep";
            param.Value = string.IsNullOrEmpty(congDanInput.NgheNghiep) ? string.Empty : congDanInput.NgheNghiep;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@HoTenChaMe";
            param.Value = string.IsNullOrEmpty(congDanInput.HoTenChaMe) ? string.Empty : congDanInput.HoTenChaMe;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@DonVi";
            param.Value = string.IsNullOrEmpty(congDanInput.DonVi) ? string.Empty : congDanInput.DonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@GhiChu";
            param.Value = string.IsNullOrEmpty(congDanInput.GhiChu) ? string.Empty : congDanInput.GhiChu;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// Insert this object CongDan into database.
        /// </summary>
        /// <param name="congDanInput">The inserting CongDan object</param>
        /// <returns>True if insert successfully, false if not</returns>
        private static bool InsertCongDan(DbCommand comm, DTO_CongDan congDanInput)
        {
            // get a configured DbCommand object
            //DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText =
                "INSERT INTO CongDan " +
                "([TenCD], [NamSinh], [HocVan], [DanToc], [TonGiao], [ThuongTru], [NgheNghiep], [HoTenChaMe], [DonVi], [GhiChu]) " +
                "VALUES " +
                "(@TenCD, @NamSinh, @HocVan, @DanToc, @TonGiao, @ThuongTru, @NgheNghiep, @HoTenChaMe, @DonVi, @GhiChu)";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@TenCD";
            param.Value = string.IsNullOrEmpty(congDanInput.Ten) ? string.Empty : congDanInput.Ten;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@NamSinh";
            param.Value = congDanInput.NamSinh;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@HocVan";
            param.Value = string.IsNullOrEmpty(congDanInput.HocVan) ? string.Empty : congDanInput.HocVan;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@DanToc";
            param.Value = string.IsNullOrEmpty(congDanInput.DanToc) ? string.Empty : congDanInput.DanToc;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@TonGiao";
            param.Value = string.IsNullOrEmpty(congDanInput.TonGiao) ? string.Empty : congDanInput.TonGiao;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ThuongTru";
            param.Value = string.IsNullOrEmpty(congDanInput.ThuongTru) ? string.Empty : congDanInput.ThuongTru;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@NgheNghiep";
            param.Value = string.IsNullOrEmpty(congDanInput.NgheNghiep) ? string.Empty : congDanInput.NgheNghiep;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@HoTenChaMe";
            param.Value = string.IsNullOrEmpty(congDanInput.HoTenChaMe) ? string.Empty : congDanInput.HoTenChaMe;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@DonVi";
            param.Value = string.IsNullOrEmpty(congDanInput.DonVi) ? string.Empty : congDanInput.DonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@GhiChu";
            param.Value = string.IsNullOrEmpty(congDanInput.GhiChu) ? string.Empty : congDanInput.GhiChu;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// Update this object CongDan from database.
        /// </summary>
        /// <param name="congDanInput">The updating CongDan object</param>
        /// <returns>True if update successfully, false if not</returns>
        public static bool UpdateCongDan(DTO_CongDan congDanInput)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText =
                "UPDATE CongDan " +
                "SET [TenCD] = @TenCD, [NamSinh] = @NamSinh, [HocVan] = @HocVan, [DanToc] = @DanToc, [TonGiao] = @TonGiao, [ThuongTru] = @ThuongTru, [NgheNghiep] = @NgheNghiep, [HoTenChaMe] = @HoTenChaMe, [DonVi] = @DonVi, [GhiChu] = @GhiChu" +
                "WHERE [MaCD] = @MA";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MA";
            param.Value = congDanInput.Ma;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@TenCD";
            param.Value = string.IsNullOrEmpty(congDanInput.Ten) ? string.Empty : congDanInput.Ten;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@NamSinh";
            param.Value = congDanInput.NamSinh;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@HocVan";
            param.Value = string.IsNullOrEmpty(congDanInput.HocVan) ? string.Empty : congDanInput.HocVan;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@DanToc";
            param.Value = string.IsNullOrEmpty(congDanInput.DanToc) ? string.Empty : congDanInput.DanToc;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@TonGiao";
            param.Value = string.IsNullOrEmpty(congDanInput.TonGiao) ? string.Empty : congDanInput.TonGiao;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@ThuongTru";
            param.Value = string.IsNullOrEmpty(congDanInput.ThuongTru) ? string.Empty : congDanInput.ThuongTru;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@NgheNghiep";
            param.Value = string.IsNullOrEmpty(congDanInput.NgheNghiep) ? string.Empty : congDanInput.NgheNghiep;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@HoTenChaMe";
            param.Value = string.IsNullOrEmpty(congDanInput.HoTenChaMe) ? string.Empty : congDanInput.HoTenChaMe;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@DonVi";
            param.Value = string.IsNullOrEmpty(congDanInput.DonVi) ? string.Empty : congDanInput.DonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@GhiChu";
            param.Value = string.IsNullOrEmpty(congDanInput.GhiChu) ? string.Empty : congDanInput.GhiChu;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// Insert list of CongDan into database.
        /// </summary>
        /// <param name="arrCongDanInput">The inserting CongDan object</param>
        /// <returns>True if insert successfully, false if not</returns>
        public static bool InsertCongDan(List<DTO_CongDan> arrCongDanInput)
        {
            DbCommand comm = GenericDataAccess.CreateCommand();
            for (int i = 0; i < arrCongDanInput.Count; i++)
            {
                if (!InsertCongDan(comm, arrCongDanInput[i]))
                    return false;
            }
            return true;

        }

        /// <summary>
        /// Delete this object CongDan from database.
        /// </summary>
        /// <param name="congDanInput">The deleting CongDan object</param>
        /// <returns>True if delete successfully, false if not</returns>
        public static bool DeleteCongDan(DTO_CongDan congDanInput)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();

            comm.CommandText =
                "DELETE CongDan " +
                "WHERE [MaCD] = @MA";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MA";
            param.Value = congDanInput.Ma;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the command
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// Retrieves all CongDan in database.
        /// </summary>
        /// <returns>List of all CongDan.</returns>
        public static List<DTO_CongDan> GetListCongDan()
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "SELECT * FROM CongDan";

            List<DTO_CongDan> congDanList = new List<DTO_CongDan>();
            // execute the command
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_CongDan curCongDan = new DTO_CongDan();
                curCongDan.Ma = (int)dr["MaCD"];
                curCongDan.Ten = (dr["TenCD"] == System.DBNull.Value) ? string.Empty : (string)dr["TenCD"];
                curCongDan.NamSinh = (int)dr["NamSinh"];
                curCongDan.HocVan = (dr["HocVan"] == System.DBNull.Value) ? string.Empty : (string)dr["HocVan"];
                curCongDan.DanToc = (dr["DanToc"] == System.DBNull.Value) ? string.Empty : (string)dr["DanToc"];
                curCongDan.TonGiao = (dr["TonGiao"] == System.DBNull.Value) ? string.Empty : (string)dr["TonGiao"];
                curCongDan.ThuongTru = (dr["ThuongTru"] == System.DBNull.Value) ? string.Empty : (string)dr["ThuongTru"];
                curCongDan.NgheNghiep = (dr["NgheNghiep"] == System.DBNull.Value) ? string.Empty : (string)dr["NgheNghiep"];
                curCongDan.HoTenChaMe = (dr["HoTenChaMe"] == System.DBNull.Value) ? string.Empty : (string)dr["HoTenChaMe"];
                curCongDan.DonVi = (dr["DonVi"] == System.DBNull.Value) ? string.Empty : (string)dr["DonVi"];
                curCongDan.GhiChu = (dr["GhiChu"] == System.DBNull.Value) ? string.Empty : (string)dr["GhiChu"];

                congDanList.Add(curCongDan);
            }
            return congDanList;
        }

        /// <summary>
        /// Retrieves CongDan by ID in database.
        /// </summary>
        /// <returns>CongDan with given ID.</returns>
        public static DTO_CongDan GetCongDan(int MaCD)
        {
            try
            {
                // get a configured DbCommand object
                DbCommand comm = GenericDataAccess.CreateCommand();
                comm.CommandText = "SELECT * FROM CongDan WHERE [MaCD] = @MA";

                DbParameter param = comm.CreateParameter();
                param.ParameterName = "@MA";
                param.Value = MaCD;
                param.DbType = DbType.Int32;
                comm.Parameters.Add(param);

                DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
                DataRow dr = dt.Rows[0];
                DTO_CongDan curCongDan = new DTO_CongDan();
                curCongDan.Ma = (int)dr["MaCD"];
                curCongDan.Ten = (dr["TenCD"] == System.DBNull.Value) ? string.Empty : (string)dr["TenCD"];
                curCongDan.NamSinh = (int)dr["NamSinh"];
                curCongDan.HocVan = (dr["HocVan"] == System.DBNull.Value) ? string.Empty : (string)dr["HocVan"];
                curCongDan.DanToc = (dr["DanToc"] == System.DBNull.Value) ? string.Empty : (string)dr["DanToc"];
                curCongDan.TonGiao = (dr["TonGiao"] == System.DBNull.Value) ? string.Empty : (string)dr["TonGiao"];
                curCongDan.ThuongTru = (dr["ThuongTru"] == System.DBNull.Value) ? string.Empty : (string)dr["ThuongTru"];
                curCongDan.NgheNghiep = (dr["NgheNghiep"] == System.DBNull.Value) ? string.Empty : (string)dr["NgheNghiep"];
                curCongDan.HoTenChaMe = (dr["HoTenChaMe"] == System.DBNull.Value) ? string.Empty : (string)dr["HoTenChaMe"];
                curCongDan.DonVi = (dr["DonVi"] == System.DBNull.Value) ? string.Empty : (string)dr["DonVi"];
                curCongDan.GhiChu = (dr["GhiChu"] == System.DBNull.Value) ? string.Empty : (string)dr["GhiChu"];
                return curCongDan;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Retrieves all CongDan in database.
        /// </summary>
        /// <returns>List of all CongDan.</returns>
        public static List<DTO_CongDan> SearchCongDan(ref DbCommand comm, DTO_CongDan congDanSearchCondition)
        {
            bool flag = false;
            // get a configured DbCommand object
            comm = GenericDataAccess.CreateCommand();
            comm.CommandText = "SELECT * FROM CongDan WHERE 1 ";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@TenCD";
            if (string.IsNullOrEmpty(congDanSearchCondition.Ten) == false)
            {
                comm.CommandText += "AND [TenCD] LIKE @TenCD ";
                param.Value = "%" + congDanSearchCondition.Ten + "%";
                param.DbType = DbType.String;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@NamSinh";
            if (congDanSearchCondition.NamSinh > 0)
            {
                comm.CommandText += "AND [NamSinh] = @NamSinh ";
                param.Value = congDanSearchCondition.NamSinh;
                param.DbType = DbType.Int32;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@HocVan";
            if (string.IsNullOrEmpty(congDanSearchCondition.HocVan) == false)
            {
                comm.CommandText += "AND [HocVan] LIKE @HocVan ";
                param.Value = "%" + congDanSearchCondition.HocVan + "%";
                param.DbType = DbType.String;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@DanToc";
            if (string.IsNullOrEmpty(congDanSearchCondition.DanToc) == false)
            {
                comm.CommandText += "AND [DanToc] LIKE @DanToc ";
                param.Value = "%" + congDanSearchCondition.DanToc + "%";
                param.DbType = DbType.String;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@TonGiao";
            if (string.IsNullOrEmpty(congDanSearchCondition.TonGiao) == false)
            {
                comm.CommandText += "AND [TonGiao] LIKE @TonGiao ";
                param.Value = "%" + congDanSearchCondition.TonGiao + "%";
                param.DbType = DbType.String;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@ThuongTru";
            if (string.IsNullOrEmpty(congDanSearchCondition.ThuongTru) == false)
            {
                comm.CommandText += "AND [ThuongTru] LIKE @ThuongTru ";
                param.Value = "%" + congDanSearchCondition.ThuongTru + "%";
                param.DbType = DbType.String;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@NgheNghiep";
            if (string.IsNullOrEmpty(congDanSearchCondition.NgheNghiep) == false)
            {
                comm.CommandText += "AND [NgheNghiep] LIKE @NgheNghiep ";
                param.Value = "%" + congDanSearchCondition.NgheNghiep + "%";
                param.DbType = DbType.String;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@HoTenChaMe";
            if (string.IsNullOrEmpty(congDanSearchCondition.HoTenChaMe) == false)
            {
                comm.CommandText += "AND [HoTenChaMe] LIKE @HoTenChaMe ";
                param.Value = "%" + congDanSearchCondition.HoTenChaMe + "%";
                param.DbType = DbType.String;
                comm.Parameters.Add(param);
                flag = true;
            }
            param = comm.CreateParameter();
            param.ParameterName = "@DonVi";
            if (string.IsNullOrEmpty(congDanSearchCondition.DonVi) == false)
            {
                comm.CommandText += "AND [MaDonVi] LIKE @DonVi ";
                param.Value = "%" + congDanSearchCondition.DonVi + "%";
                param.DbType = DbType.String;
                comm.Parameters.Add(param);
                flag = true;
            }
            List<DTO_CongDan> congDanList = new List<DTO_CongDan>();
            if (flag == true)
            {
                DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
                foreach (DataRow dr in dt.Rows)
                {
                    DTO_CongDan curCongDan = new DTO_CongDan();
                    curCongDan.Ma = (int)dr["MaCD"];
                    curCongDan.Ten = (dr["TenCD"] == System.DBNull.Value) ? string.Empty : (string)dr["TenCD"];
                    curCongDan.NamSinh = (dr["NamSinh"] == System.DBNull.Value) ? -1 : Convert.ToInt32(dr["NamSinh"]);
                    curCongDan.HocVan = (dr["HocVan"] == System.DBNull.Value) ? string.Empty : (string)dr["HocVan"];
                    curCongDan.DanToc = (dr["DanToc"] == System.DBNull.Value) ? string.Empty : (string)dr["DanToc"];
                    curCongDan.TonGiao = (dr["TonGiao"] == System.DBNull.Value) ? string.Empty : (string)dr["TonGiao"];
                    curCongDan.ThuongTru = (dr["ThuongTru"] == System.DBNull.Value) ? string.Empty : (string)dr["ThuongTru"];
                    curCongDan.NgheNghiep = (dr["NgheNghiep"] == System.DBNull.Value) ? string.Empty : (string)dr["NgheNghiep"];
                    curCongDan.HoTenChaMe = (dr["HoTenChaMe"] == System.DBNull.Value) ? string.Empty : (string)dr["HoTenChaMe"];
                    curCongDan.DonVi = (dr["DonVi"] == System.DBNull.Value) ? string.Empty : (string)dr["DonVi"];
                    curCongDan.GhiChu = (dr["GhiChu"] == System.DBNull.Value) ? string.Empty : (string)dr["GhiChu"];

                    congDanList.Add(curCongDan);
                }
            }
            return congDanList;
        }

        /// <summary>
        /// Retrieves all CongDan in database.
        /// </summary>
        /// <returns>The OleDBCommand of this search query.</returns>
        public static OleDbCommand OleDBCommandSearchCongDan(DTO_CongDan congDanSearchCondition)
        {
            bool flag = false;
            // get a configured DbCommand object

            OleDbCommand comm = GenericDataAccess.CreateOleDbCommand();
            comm.CommandText = "SELECT * FROM CongDan WHERE 1 ";

            OleDbParameter param = comm.CreateParameter();

            if (string.IsNullOrEmpty(congDanSearchCondition.Ten) == false)
            {
                comm.CommandText += "AND [TenCD] LIKE ? ";
                param.Value = "%" + congDanSearchCondition.Ten + "%";
                param.OleDbType = OleDbType.VarChar;
                param.Size = 25;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@NamSinh";
            if (congDanSearchCondition.NamSinh > 0)
            {
                comm.CommandText += "AND [NamSinh] = @NamSinh ";
                param.Value = congDanSearchCondition.NamSinh;
                param.DbType = DbType.Int32;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@HocVan";
            if (string.IsNullOrEmpty(congDanSearchCondition.HocVan) == false)
            {
                comm.CommandText += "AND [HocVan] LIKE @HocVan ";
                param.Value = "%" + congDanSearchCondition.HocVan + "%";
                param.OleDbType = OleDbType.VarChar;
                param.Size = 25;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@DanToc";
            if (string.IsNullOrEmpty(congDanSearchCondition.DanToc) == false)
            {
                comm.CommandText += "AND [DanToc] LIKE @DanToc ";
                param.Value = "%" + congDanSearchCondition.DanToc + "%";
                param.OleDbType = OleDbType.VarChar;
                param.Size = 25;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@TonGiao";
            if (string.IsNullOrEmpty(congDanSearchCondition.TonGiao) == false)
            {
                comm.CommandText += "AND [TonGiao] LIKE @TonGiao ";
                param.Value = "%" + congDanSearchCondition.TonGiao + "%";
                param.OleDbType = OleDbType.VarChar;
                param.Size = 25;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@ThuongTru";
            if (string.IsNullOrEmpty(congDanSearchCondition.ThuongTru) == false)
            {
                comm.CommandText += "AND [ThuongTru] LIKE @ThuongTru ";
                param.Value = "%" + congDanSearchCondition.ThuongTru + "%";
                param.OleDbType = OleDbType.VarChar;
                param.Size = 25;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@NgheNghiep";
            if (string.IsNullOrEmpty(congDanSearchCondition.NgheNghiep) == false)
            {
                comm.CommandText += "AND [NgheNghiep] LIKE @NgheNghiep ";
                param.Value = "%" + congDanSearchCondition.NgheNghiep + "%";
                param.OleDbType = OleDbType.VarChar;
                param.Size = 25;
                comm.Parameters.Add(param);
                flag = true;
            }

            param = comm.CreateParameter();
            param.ParameterName = "@HoTenChaMe";
            if (string.IsNullOrEmpty(congDanSearchCondition.HoTenChaMe) == false)
            {
                comm.CommandText += "AND [HoTenChaMe] LIKE @HoTenChaMe ";
                param.Value = "%" + congDanSearchCondition.HoTenChaMe + "%";
                param.OleDbType = OleDbType.VarChar;
                param.Size = 25;
                comm.Parameters.Add(param);
                flag = true;
            }
            param = comm.CreateParameter();
            param.ParameterName = "@DonVi";
            if (string.IsNullOrEmpty(congDanSearchCondition.DonVi) == false)
            {
                comm.CommandText += "AND [MaDonVi] = @DonVi ";
                param.Value = congDanSearchCondition.DonVi;
                param.OleDbType = OleDbType.VarChar;
                param.Size = 25;
                comm.Parameters.Add(param);
                flag = true;
            }

            if (flag == false)
            {
                comm.CommandText = "SELECT * FROM CongDan WHERE 0";
            }
            return comm;
        }


        /// <summary>
        /// Update CongDan table
        /// </summary>
        public static void UpdateCongDanTable(DataSet ds)
        {
            System.Data.OleDb.OleDbConnection cn = GenericDataAccess.GetConnection();
            string strSQL = "Select * From CongDan";
            //B4: Thuc thi chuoi strSQL
            System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strSQL, cn);
            System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);
            da.Update(ds.Tables[0]);
            cn.Close();
        }

        /// <summary>
        /// Update CongDan table with given command
        /// </summary>
        public static void UpdateCongDanTable(OleDbCommand comm, DataSet ds)
        {
            OleDbDataAdapter da = new OleDbDataAdapter(comm);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
            da.Update(ds.Tables[0]);
        }

        /// <summary>
        /// Get CongDan table
        /// </summary>
        /// <returns>The dataset of CongDan table</returns>
        public static DataSet GetCongDanTable()
        {
            System.Data.OleDb.OleDbConnection cn = GenericDataAccess.GetConnection();
            string strSQL = "Select * From CongDan";
            System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strSQL, cn);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source);
            da.Fill(ds);
            cn.Close();
            return ds;
        }

        /// <summary>
        /// Get CongDan table
        /// </summary>
        /// <returns>The dataset of CongDan table</returns>
        public static DataSet GetCongDanTable(OleDbCommand comm)
        {
            OleDbDataAdapter da = new OleDbDataAdapter(comm);
            DataSet ds = new DataSet();
            da.FillSchema(ds, SchemaType.Source);
            da.Fill(ds);
            return ds;
        }

        public static bool InsertCongDan(string MaDonVi, List<DTO_Field> listCongDanValue)
        {
            string fieldStr = "(MaDonVi,";
            foreach (DTO_Field curField in listCongDanValue)
            {
                fieldStr += curField.TenField + ",";
            }
            fieldStr = fieldStr.Substring(0, fieldStr.Length - 1);
            fieldStr += ")";

            string sqlStr =
                "INSERT INTO CongDan " +
                fieldStr +
                "VALUES " +
                "(";

            for (int i = 0; i < listCongDanValue.Count + 1; i++)
            {
                sqlStr += "?,";
            }
            sqlStr = sqlStr.Substring(0, sqlStr.Length - 1);
            sqlStr += ")";

            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            comm.CommandText = sqlStr;

            DbParameter param = comm.CreateParameter();
            param.Value = string.IsNullOrEmpty(MaDonVi) ? string.Empty : MaDonVi;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            foreach (DTO_Field curField in listCongDanValue)
            {
                param = comm.CreateParameter();

                param.DbType = DbType.String;
                param.Value = Convert.ToString(curField.GiaTriThuc);

                comm.Parameters.Add(param);
            }

            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }
    }
}

